CREATE PROCEDURE SP_CANCEL_DIST_SALES_INVENTORY_DETAILS
(
   @DISTRIBUTOR_ID INT,
   @PRODUCT_ID     INT,
   @QTY_IN	   INT		 	
)
AS
BEGIN
	DECLARE @PROCESSSTEP_ID INT
	DECLARE @CSLIT_ID INT
	DECLARE @LOT_ID INT
	DECLARE @QTY_SHIPPED INT

	DECLARE @TEMPCURSOR		CURSOR

	SELECT @PROCESSSTEP_ID = PROCESSSTEP_ID FROM PRODUCT WHERE PRODUCT_ID = @PRODUCT_ID
		
	SET @TEMPCURSOR = CURSOR FOR SELECT Cslit_id, lot_id, Shipped_QTY from Distributor_Inventory_lot where PROCESSSTEP_Id = @PROCESSSTEP_ID AND CUSTOMER_ID = @DISTRIBUTOR_ID and shipped_qty is not null ORDER BY DATE_IN DESC

	OPEN @TEMPCURSOR
		FETCH NEXT FROM @TEMPCURSOR INTO @CSLIT_ID, @LOT_ID, @QTY_SHIPPED
		
		WHILE @@FETCH_STATUS = 0
		BEGIN
			IF @QTY_IN <= @QTY_SHIPPED
			BEGIN
				UPDATE Distributor_Inventory_lot SET Shipped_QTY = 0 WHERE PROCESSSTEP_Id = @PROCESSSTEP_ID AND CUSTOMER_ID = @DISTRIBUTOR_ID AND LOT_ID = @LOT_ID AND CSLIT_ID = @CSLIT_ID
				SET @QTY_SHIPPED = @QTY_SHIPPED - @QTY_IN		
			END
			ELSE
			BEGIN
				UPDATE Distributor_Inventory_lot SET Shipped_QTY = Shipped_QTY-@QTY_SHIPPED WHERE PROCESSSTEP_Id = @PROCESSSTEP_ID AND CUSTOMER_ID = @DISTRIBUTOR_ID AND LOT_ID = @LOT_ID AND CSLIT_ID = @CSLIT_ID
				SET @QTY_SHIPPED = 0 	
			END 
						
			SET @LOT_ID 		= 0 
			SET @CSLIT_ID		= 0 

			IF @QTY_SHIPPED = 0
			BEGIN
				BREAK
			END		
		FETCH NEXT FROM @TEMPCURSOR INTO @CSLIT_ID, @LOT_ID, @QTY_SHIPPED
		END
		CLOSE @TEMPCURSOR
		DEALLOCATE @TEMPCURSOR
END